1. Field of Use
The present invention relates to database systems and methods for managing information and more particularly to systems and methods for storing information pertaining to manufacturing assembly information wherein several pieces of such information are combined to produce a bill of materials document.
2. Prior Art
In the manufacturing assembly process, it is essential that for each assembly manufactured by an organization pertinent product related information for currently produced and previously produced assemblies be made available to all personnel involved in such process. In order to do this, the organization generally provides a network system environment wherein all users are given access to a single reference source of information maintained on a server system from a number of client stations.
For each assembly manufactured by the organization, there is a need to be able to understand which device components are used on the assembly, where the components are located on the assembly and where such component parts can be purchased. This information is generally compiled into a format usable by the organization and is more commonly known as a "bill of materials" (BOM). In producing a BOM document, several pieces of information must be brought together. The BOM document generally includes: (1) a listing of the material required usually arranged according to customer part number; (2) the number of items having a particular part number for the specific assembly; (3) a description of the component part that identifies what the part is (e.g. resistor, capacitor, etc.); (4) the vendor or vendors sources from which the component part can be purchased; (5) the vendor source's part number for the component part; and (6) the location on the assembly where the component part is to be installed. The location is usually defined in terms of some topographical reference, such as U1, etc. One of these references is allocated for each component part type used on the assembly.
The bill of materials for the printed circuit board assembly is generally constructed to contain the information for each component part to be used on the assembly. More specifically, the bill of materials document would contain the following information for each component part:
(1) The name of the component using a customer or internal designation that allows the component to be distinguishable from any other component used or received by the manufacturing organization. For example, the component name may be based on the customer's part number for that component part; PA1 (2) A description of the component part in terms that define its physical or electronic attributes/characteristics. This information may appear on the surface of the component part; PA1 (3) The physical location on the printed circuit board assembly where the component can be found. Reference locations are typically related to a design print or may be marked directly on the printed circuit board; PA1 (4) The manufacturers of the component part (vendor) from which the board manufacturer may purchase the component part. Where there are multiple vendors approved as sources for the component part, multiple entries are required. PA1 (5) The vendor designation for the component part that the manufacturer's purchasing department would use to purchase the part. Each vendor typically assigns their own part number designation.
In a prior art method, several of the above information items representative of product information were stored in a database structure (i.e., Product Table) which when read into an EXCEL spreadsheet would appear as follows.
______________________________________ Prefix Board Rev Ref. Desig Component Other data ______________________________________ AA AA020-0001 A R01 191-0014 N/A AA AA020-0001 A R02 191-0017 N/A AA AA020-0001 A R15 191-0014 N/A AA AA020-0001 A R27 191-0014 N/A AA AA020-0001 A R43 191-0054 N/A ______________________________________
In this method, the number of entries in the file representing the product table would be made equal to the number of reference designators in the printed circuit board assembly. While this method was adequate for storing such information, and for providing reasonably fast access, it was found to require storage of a considerable amount of repetitive or redundant information in the database. This was not deemed to be an insurmountable problem except when there was a system requirement for revision retention for tracking and quality purposes. In order to insure that all customer information was retained for some indeterminate period of time, then it became necessary to preserve the component parts list for each revision of the product data each time a product change notice was received. Thus, for a simple change of one component part type, it became necessary to duplicate all of the entries in the product list and assign a new reference designation for the product revision. For example, for the single component change, the product file would have to be duplicated for each revision with the result that the updated database would appear as follows.
______________________________________ Prefix Board Rev Ref. Desig Component Other data ______________________________________ AA AA020-0001 A R01 191-0014 N/A AA AA020-0001 A R02 191-0017 N/A AA AA020-0001 A R15 191-0014 N/A AA AA020-0001 A R27 191-0014 N/A AA AA020-0001 A R43 191-0054 N/A AA AA020-0001 A1 R01 191-0014 N/A AA AA020-0001 A1 R02 191-0017 N/A AA AA020-0001 A1 R15 191-0014 N/A AA AA020-0001 A1 R27 191-0057 N/A AA AA020-0001 A1 R43 191-0054 N/A ______________________________________
In the above example, reversion A1 of the printed circuit board AA020-0001 had a single component part change (R27) designated as 191-0014 and the revision now is to be populated with different component part R27 designated as 191-0057. While this example shows a small change, it still illustrates that each change to a printed circuit board product results in a complete duplication of all of the database entries for that version of the product wherein the actual changes are incorporated in those entries.
Additionally, in order to provide a reasonably performant system, it is necessary to have a short table of products contained in the database to define the products contained in that database. In the prior art method, a conventional query performed on the database to obtain a sample of all available entries would require the use of several systems and would grow as the file size grew. That is, in the prior art method, each time the product data was updated, a single record had to be placed in an index table in order to define what is in the product table. Thus, the prior art method was found to be less performant unless expanded hardware was introduced into the system.
In the case of the tables used for storing component and approved vendor information, the prior art method did not provide for retaining information from one update to the next but rather just replaced the old information in the database. Thus, there was no provision for historical information capable of being retrieved for any of the component and approved vendor information items aside from operator comments that were recorded in a separate history tracking file.
Accordingly, it is a primary object of the present invention to provide a method and system for eliminating the amount of duplicate information contained in a manufacturing database.
It is a further object of the present invention to provide a method and system that is able to provide historical information with minimum increase in database storage requirements.